<?php

class CariController extends Controller
{
	public $message;
	public function actionIndex()
	{		
		//if($dariCari != "" && $keCari != "" && $dateCari != "")
		if(isset($_POST['Search']['dari']) && !empty($_POST['Search']['dari']) && isset($_POST['Search']['ke']) && !empty($_POST['Search']['dari']) && isset($_POST['Search']['ke']) && !empty($_POST['Search']['date']))
		{
			$dariCari = $_POST['Search']['dari'];
			$keCari = $_POST['Search']['ke'];
			$dateCari = $_POST['Search']['date'];
			
			$this->message =  $dariCari . " " . $keCari . " " . $dateCari;	
			//SELECT nama_kota FROM kota where id_kota = '12';
			$dariCriteria = new CDbCriteria();
			$dariCriteria->select = "NAMA_KOTA";
			$dariCriteria->condition = "ID_KOTA = '" . $dariCari . "'";
			
			$keCriteria = new CDbCriteria();
			$keCriteria->select = "NAMA_KOTA";
			$keCriteria->condition = "ID_KOTA = '" . $keCari . "'";
			
			/*select ka.nama_kereta, dk.kelas_kereta, dk.durasi_kereta, dk.harga_tiket, dk.jam_berangkat, dk.jam_pulang
			from kereta_api ka left join detail_perjalanan_kereta dk on ka.id_kereta = dk.id_kereta
			where dk.tgl_berangkat = '2011-09-01' and dk.id_kota = 12 and dk.kot_id_kota = 11*/			
			$sqlKereta = "
                                    SELECT
                                        ka.NAMA_KERETA, ka.ID_KERETA, dk.KELAS_KERETA, dk.DURASI_KERETA, dk.HARGA_TIKET, dk.JAM_BERANGKAT, dk.JAM_PULANG, dk.DESKRIPSI_KERETA, dk.DURASI_KERETA
                                    FROM
                                        kereta_api ka LEFT JOIN detail_perjalanan_kereta dk ON ka.ID_KERETA = dk.ID_KERETA
                                    WHERE
                                        dk.TGL_BERANGKAT = '" . $dateCari . "' AND dk.ID_KOTA = " . $dariCari . " AND dk.KOT_ID_KOTA = " . $keCari;
			
			
			/*select b.nama_bus, db.harga_tiket, db.jam_berangkat from bus b left join detail_perjalanan_bus db on b.id_bus = db.id_bus where db.kot_id_kota = 'Sampang' and db.id_kota = 'Surabaya'*/
			$sqlBus = "SELECT b.NAMA_BUS, b.ID_BUS, db.JAM_BERANGKAT, db.JAM_PULANG, db.HARGA_TIKET, db.DURASI_BUS
						FROM bus b LEFT JOIN detail_perjalanan_bus db ON b.ID_BUS = db.ID_BUS
						WHERE db.ID_KOTA = " . $dariCari . " AND db.KOT_ID_KOTA = " . $keCari;
			
			/*SELECT t.ID_TRAVEL, t.NAMA_TRAVEL, dt.JAM_BERANGKAT, dt.JAM_PULANG, dt.HARGA_TIKET, dt.KELAS_TRAVEL
FROM penyedia_travel t JOIN detail_perjalanan_travel dt ON t.ID_TRAVEL = dt.ID_TRAVEL
WHERE dt.ID_KOTA = 12 AND dt.KOT_ID_KOTA = 11*/
			$sqlTravel = "SELECT t.ID_TRAVEL, t.NAMA_TRAVEL, dt.ID_DETAIL_TRAVEL, dt.JAM_BERANGKAT, dt.JAM_PULANG, dt.HARGA_TIKET, dt.KELAS_TRAVEL, dt.DURASI_TRAVEL
							FROM penyedia_travel t JOIN detail_perjalanan_travel dt ON t.ID_TRAVEL = dt.ID_TRAVEL
							WHERE dt.ID_KOTA = "  . $dariCari . " AND dt.KOT_ID_KOTA = "  . $keCari ;
						
			$sqlKeretaMurah = "SELECT dk.HARGA_TIKET
                               FROM detail_perjalanan_kereta dk
                               WHERE dk.TGL_BERANGKAT = '" . $dateCari . "' AND dk.ID_KOTA = " . $dariCari . " AND dk.KOT_ID_KOTA = " . $keCari . 
							   " ORDER BY dk.HARGA_TIKET
							   LIMIT 1";
			$sqlBusMurah = "SELECT db.HARGA_TIKET
                               FROM detail_perjalanan_bus db
                               WHERE db.ID_KOTA = " . $dariCari . " AND db.KOT_ID_KOTA = " . $keCari . 
							   " ORDER BY db.HARGA_TIKET
							   LIMIT 1";
			$sqlTravelMurah = "SELECT dt.HARGA_TIKET
                               FROM detail_perjalanan_travel dt
                               WHERE dt.ID_KOTA = " . $dariCari . " AND dt.KOT_ID_KOTA = " . $keCari . 
							   " ORDER BY dt.HARGA_TIKET
							   LIMIT 1";
							   
			//tercepat
			$sqlKeretaCepat = "SELECT dk.DURASI_KERETA
                               FROM detail_perjalanan_kereta dk
                               WHERE dk.TGL_BERANGKAT = '" . $dateCari . "' AND dk.ID_KOTA = " . $dariCari . " AND dk.KOT_ID_KOTA = " . $keCari . 
							   " ORDER BY dk.DURASI_KERETA
							   LIMIT 1";
			$sqlBusCepat = "SELECT db.DURASI_BUS
                               FROM detail_perjalanan_bus db
                               WHERE db.ID_KOTA = " . $dariCari . " AND db.KOT_ID_KOTA = " . $keCari . 
							   " ORDER BY db.DURASI_BUS
							   LIMIT 1";
			$sqlTravelCepat = "SELECT dt.DURASI_TRAVEL
                               FROM detail_perjalanan_travel dt
                               WHERE dt.ID_KOTA = " . $dariCari . " AND dt.KOT_ID_KOTA = " . $keCari . 
							   " ORDER BY  dt.DURASI_TRAVEL
							   LIMIT 1";
							   
			$keretas = Yii::app()->db->createCommand($sqlKereta)->queryAll();
			$buss = Yii::app()->db->createCommand($sqlBus)->queryAll();
			$travels = Yii::app()->db->createCommand($sqlTravel)->queryAll();
			
			//termurah
			$keretaM = Yii::app()->db->createCommand($sqlKeretaMurah)->queryAll();
			$busM = Yii::app()->db->createCommand($sqlBusMurah)->queryAll();
			$travelM = Yii::app()->db->createCommand($sqlTravelMurah)->queryAll();
            
			//tercepat
			$keretaC = Yii::app()->db->createCommand($sqlKeretaCepat)->queryAll();
			$busC = Yii::app()->db->createCommand($sqlBusCepat)->queryAll();
			$travelC = Yii::app()->db->createCommand($sqlTravelCepat)->queryAll();
			
			$this->render('index',array(//'Keretas' => KeretaApi::model()->with('detailPerjalananKeretas')->findAll($keretaCriteria),
										'Keretas' => $keretas,
										'Buss' => $buss,
										'Travels' => $travels,
										'KeretaM' => $keretaM,
										'BusM' => $busM,
										'TravelM' => $travelM,
										'KeretaC' => $keretaC,
										'BusC' => $busC,
										'TravelC' => $travelC,
										'Daris' => Kota::model()->findAll($dariCriteria),
										'Kes' => Kota::model()->findAll($keCriteria),
										'jmlKereta' => count($keretas),
										'jmlBus' => count($buss),
										'jmlTravel' => count($travels)));
		}
		else
		{
			$this->message =  "Silahkan pilih kota asal, kota tujuan, dan tanggal keberangkatan";
			$this->render('index', array('content'=>$this->message));
		}
		
	}
	
	/*public function actionPencarian()
	{
		
	}*/

	// Uncomment the following methods and override them if needed
	/*
	public function filters()
	{
		// return the filter configuration for this controller, e.g.:
		return array(
			'inlineFilterName',
			array(
				'class'=>'path.to.FilterClass',
				'propertyName'=>'propertyValue',
			),
		);
	}

	public function actions()
	{
		// return external action classes, e.g.:
		return array(
			'action1'=>'path.to.ActionClass',
			'action2'=>array(
				'class'=>'path.to.AnotherActionClass',
				'propertyName'=>'propertyValue',
			),
		);
	}
	*/
}?>